import sys
import MySQLdb

def searchSocialidFromSeq() :
	db = MySQLdb.connect(host="172.16.1.163", user="gamevil",passwd="vlwlrpaqlf",db="pz_user")
	cursor = db.cursor()

	openfile = file("input.txt", "r")
	writefile = file("output.txt", "w")

	for data in openfile.readlines() :
		value = data.strip()
		query = "select socialid from info_login where seq = %s" % value
		print query
		cursor.execute(query)
		rows = cursor.fetchall()
		for r in rows :
			result = "seq : \t%s \t socialid : \t%s\n" % (value, r[0])
			print result
			writefile.write(result)
	
	cursor.close()
	openfile.close()
	writefile.close()
		

def searchSeqFromSocialid() :
	db = MySQLdb.connect(host="172.16.1.163", user="gamevil",passwd="vlwlrpaqlf",db="pz_user")
	cursor = db.cursor()

	openfile = file("input.txt", "r")
	writefile = file("output.txt", "w")

	for data in openfile.readlines() :
		value = data.strip()
		query = """select seq from info_login where socialid = '%s'""" % value
		print query
		cursor.execute(query)
		rows = cursor.fetchall()
		for r in rows :
			result = "socialid : \t%s \t seq : \t%s\n" % (value, r[0])
			print result
			writefile.write(result)
	
	cursor.close()
	openfile.close()
	writefile.close()

def pvpRankAndFriends() :
	db = MySQLdb.connect(host="172.16.1.163", user="gamevil",passwd="vlwlrpaqlf",db="pz_user")
	cursor = db.cursor()

	openfile = file("input.txt", "r")
	writefile = file("output.txt", "w")

	for data in openfile.readlines() :
		value = data.strip()
		query = """	
			select b.score, b.heart_blocked
			from info_login as a 
			inner join info_user as b on a.seq = b.seq and a.socialid = '%s';
		""" % value

		print query
		cursor.execute(query)
		rows = cursor.fetchall()
		for r in rows :
			result = "socialid : \t%s\t ranking: \t%s\t frineds : \t%s \n" % (value, r[0], r[1])
			print result
			writefile.write(result)
	
	cursor.close()
	openfile.close()
	writefile.close()


def vchistory() :
	print 'vchistory'
	array = []
	array.append("172.16.2.36")
	array.append("172.16.1.183")
	array.append("172.16.2.40")
	array.append("172.16.2.37")
	array.append("172.16.2.38")
	array.append("172.16.2.39")

	for ip in array :
		db = MySQLdb.connect(host=ip, user="gamevil",passwd="vlwlrpaqlf",db="pz_user")
		cursor = db.cursor()

		writefile = file(ip + ".txt", "w")
		query = """	
			select socialid from 
			(
				select socialid from vc_hist_data_201312 where ref_vc_division1 = 0 and ref_vc_division2 in (1,2,3,4)
				union
				select socialid from vc_hist_data_201401 where ref_vc_division1 = 0 and ref_vc_division2 in (1,2,3,4)
				union
				select socialid from vc_hist_data_201402 where ref_vc_division1 = 0 and ref_vc_division2 in (1,2,3,4)
				union
				select socialid from vc_hist_data_201403 where ref_vc_division1 = 0 and ref_vc_division2 in (1,2,3,4)
				union
				select socialid from vc_hist_data_201404 where ref_vc_division1 = 0 and ref_vc_division2 in (1,2,3,4)
				union
				select socialid from vc_hist_data_201405 where ref_vc_division1 = 0 and ref_vc_division2 in (1,2,3,4)
				union
				select socialid from vc_hist_data_201406 where ref_vc_division1 = 0 and ref_vc_division2 in (1,2,3,4)
			) x;
		""" 

		print query
		cursor.execute(query)
		rows = cursor.fetchall()
		for r in rows :
			#result = "count : %d" % (r[0])
			print r[0] 
			writefile.write(r[0])
			writefile.write("\n")
		
		cursor.close()
		db.close()
		writefile.close()

def vchistory_count() :
	print 'vchistory_count'
	array = []
	array.append("172.16.2.36")
	array.append("172.16.1.183")
	array.append("172.16.2.40")
	array.append("172.16.2.37")
	array.append("172.16.2.38")
	array.append("172.16.2.39")

	total = []

	game_db = MySQLdb.connect(host="172.16.1.163", user="gamevil",passwd="vlwlrpaqlf",db="pz_user")
	game_cursor = game_db.cursor()
	game_query = """
		SELECT ref_vc_division1, ref_vc_division2, ref_vc_division3, ref_vc_desc 
		FROM resource_vc_list;
	"""
	game_cursor.execute(game_query)
	game_rows = game_cursor.fetchall()



	for value in game_rows :
		total.append(0)

	for ip in array :
		db = MySQLdb.connect(host=ip, user="gamevil",passwd="vlwlrpaqlf",db="pz_user")
		cursor = db.cursor()

		writefile = file(ip + ".txt", "w")
		
		index = 0
		for value in game_rows :
			cursor = db.cursor()
			query = """	
				SELECT count(*) from vc_hist_data_201408 
				WHERE ref_vc_desc = '%s' 
				AND reg_date >= '2014-08-27 00.00.00.000000' AND reg_date < '2014-08-28 00.00.00.000000';
			""" % (value[3])

			#print query
			cursor.execute(query)
			rows = cursor.fetchall()
			for r in rows :
				writefile.write(value[3])
				writefile.write("\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t")
				writefile.write("%s" % r[0])
				writefile.write("\n")
				total[index] += r[0]

			index += 1
			cursor.close()

		db.close()
		writefile.close()
		print ip + " complete"

	totalfile = file("total.txt", "w")
	totalindex = 0
	for value in game_rows :
		totalfile.write("%s\t%s" % (value[3], total[totalindex]))	
		totalfile.write("\n")
		totalindex += 1
	totalfile.close()

	game_cursor.close()
	game_db.close()



def usage() :
	print " 1: searchSocialidFromSeq"
	print " 2: searchSeqFromSocialid"
	print " 3: pvpRankAndFriendsFromSocialid"
	print " 4: vchistory"
	print " 5: vchistory_count"
	sys.exit()	

if __name__ == "__main__" :
	if (len(sys.argv) < 2) : usage()
	
	command = sys.argv[1]

	if ("1" == command) : searchSocialidFromSeq()
	if ("2" == command) : searchSeqFromSocialid()
	if ("3" == command) : pvpRankAndFriends()
	if ("4" == command) : vchistory()
	if ("5" == command) : vchistory_count()
